//
//  DBSQLQueries.h
//  Nicejinux.NET
//
//  Created by Nicejinux on 12. 3. 27..
//  Copyright (c) 2012년 Nicejinux.NET. All rights reserved.
//

#ifndef DBSQLQueries_h
#define DBSQLQueries_h

//******************************************************************//
// creation SQL queries
//******************************************************************//

#define CREATE_DBInfo               @"CREATE TABLE IF NOT EXISTS DBInfo (\
                                    dbVersion       CHAR(31));"

#define CREATE_CDNInfo               @"CREATE TABLE IF NOT EXISTS CDNInfo (\
                                    cdnPathNo       NUMERIC     , \
                                    cdnPathName     CHAR(200));"


#define CREATE_FlowerInfoList       @"CREATE TABLE IF NOT EXISTS FlowerInfoList (\
                                    flowerNo        NUMERIC,    \
                                    flowerName      CHAR(200),  \
                                    categoryOrder   NUMERIC,    \
                                    comment         CHAR(200),  \
                                    eventYN         CHAR(2),    \
                                    imgName         CHAR(50),   \
                                    imgNameSurfix   CHAR(10),   \
                                    subInfo         CHAR(100),  \
                                    newYN           CHAR(2),    \
                                    orignPrice      NUMERIC,    \
                                    salePrice       NUMERIC,    \
                                    price           NUMERIC,    \
                                    saleCategoryNo  NUMERIC,    \
                                    saleCategoryName  CHAR(50), \
                                    useCategoryNo   NUMERIC,    \
                                    useCategoryName CHAR(50),   \
                                    saleYN          CHAR(2),    \
                                    seasonOnlyYN    CHAR(2),    \
                                    soldOutYN       CHAR(2),    \
                                    useYN           CHAR(2));"

#define CREATE_SaleCategoryList       @"CREATE TABLE IF NOT EXISTS SaleCategoryList (\
saleCategoryNo      NUMERIC,    \
saleCategoryOrder   NUMERIC,    \
saleCategoryName    CHAR(50),   \
saleCategoryEventYN CHAR(2));"

#define CREATE_UseCategoryList       @"CREATE TABLE IF NOT EXISTS UseCategoryList (\
useCategoryNo      NUMERIC,    \
useCategoryOrder   NUMERIC,    \
useCategoryName    CHAR(50),   \
useCategoryEventYN CHAR(2));"

//#define CREATE_SubInfoList          @"CREATE TABLE IF NOT EXISTS FlowerSubInfoList (\
//                                    flowerNo        NUMERIC,    \
//                                    subInfo         CHAR(10))"

#define CREATE_FavoriteList         @"CREATE TABLE IF NOT EXISTS FavoriteList (\
                                    flowerNo        NUMERIC,    \
                                    flowerName      CHAR(200),  \
                                    categoryOrder   NUMERIC,    \
                                    comment         CHAR(200),  \
                                    eventYN         CHAR(2),    \
                                    imgName         CHAR(50),   \
                                    imgNameSurfix   CHAR(10),   \
                                    subInfo         CHAR(100),  \
                                    newYN           CHAR(2),    \
                                    orignPrice      NUMERIC,    \
                                    salePrice       NUMERIC,    \
                                    price           NUMERIC,    \
                                    saleCategoryNo  NUMERIC,    \
                                    saleCategoryName  CHAR(50), \
                                    useCategoryNo   NUMERIC,    \
                                    useCategoryName CHAR(50),   \
                                    saleYN          CHAR(2),    \
                                    seasonOnlyYN    CHAR(2),    \
                                    soldOutYN       CHAR(2),    \
                                    useYN           CHAR(2),    \
                                    favoritedDate   CHAR(15))"


//******************************************************************//
// insert SQL queries
//******************************************************************//

#define INSERT_DBInfo               "INSERT OR REPLACE INTO DBInfo (\
                                    dbVersion)          \
                                    VALUES              \
                                    (?1);"

#define INSERT_CDNInfo              "INSERT OR REPLACE INTO CDNInfo (\
                                    cdnPathNo   ,       \
                                    cdnPathName         \
                                    ) VALUES (          \
                                    ?1,                 \
                                    ?2);"

//#define INSERT_SubInfoList          "INSERT INTO FlowerSubInfoList (\
//                                    flowerNo,           \
//                                    subInfo)            \
//                                    VALUES              \
//                                    (?1, ?2);"

#define INSERT_SaleCategoryList          "INSERT INTO SaleCategoryList (\
saleCategoryNo,     \
saleCategoryOrder,  \
saleCategoryName,   \
saleCategoryEventYN \
) VALUES (          \
(?1, ?2, ?3, ?4);"

#define INSERT_UseCategoryList          "INSERT INTO UseCategoryList (\
useCategoryNo,     \
useCategoryOrder,  \
useCategoryName,   \
useCategoryEventYN \
) VALUES (          \
(?1, ?2, ?3, ?4);"

#define INSERT_FlowerInfoList       "INSERT INTO FlowerInfoList (\
                                    flowerNo        ,   \
                                    flowerName      ,   \
                                    categoryOrder   ,   \
                                    comment         ,   \
                                    eventYN         ,   \
                                    imgName         ,   \
                                    imgNameSurfix   ,   \
                                    subInfo         ,   \
                                    newYN           ,   \
                                    orignPrice      ,   \
                                    salePrice       ,   \
                                    price           ,   \
                                    saleCategoryNo  ,   \
                                    saleCategoryName  , \
                                    useCategoryNo   ,   \
                                    useCategoryName ,   \
                                    saleYN          ,   \
                                    seasonOnlyYN    ,   \
                                    soldOutYN       ,   \
                                    useYN               \
                                    ) VALUES (\
                                    ?1,     \
                                    ?2,     \
                                    ?3,     \
                                    ?4,     \
                                    ?5,     \
                                    ?6,     \
                                    ?7,     \
                                    ?8,     \
                                    ?9,     \
                                    ?10,    \
                                    ?11,    \
                                    ?12,    \
                                    ?13,    \
                                    ?14,    \
                                    ?15,    \
                                    ?16,    \
                                    ?17,    \
                                    ?18,    \
                                    ?19,    \
                                    ?20)"


#define INSERT_FavoriteList         @"INSERT OR REPLACE INTO FavoriteList (\
                                    flowerNo        ,   \
                                    flowerName      ,   \
                                    categoryOrder   ,   \
                                    comment         ,   \
                                    eventYN         ,   \
                                    imgName         ,   \
                                    imgNameSurfix   ,   \
                                    subInfo         ,   \
                                    newYN           ,   \
                                    orignPrice      ,   \
                                    salePrice       ,   \
                                    price           ,   \
                                    saleCategoryNo  ,   \
                                    saleCategoryName  , \
                                    useCategoryNo   ,   \
                                    useCategoryName ,   \
                                    saleYN          ,   \
                                    seasonOnlyYN    ,   \
                                    soldOutYN       ,   \
                                    useYN           ,   \
                                    favoritedDate       \
                                    ) VALUES (\
                                    ?1,     \
                                    ?2,     \
                                    ?3,     \
                                    ?4,     \
                                    ?5,     \
                                    ?6,     \
                                    ?7,     \
                                    ?8,     \
                                    ?9,     \
                                    ?10,    \
                                    ?11,    \
                                    ?12,    \
                                    ?13,    \
                                    ?14,    \
                                    ?15,    \
                                    ?16,    \
                                    ?17,    \
                                    ?18,    \
                                    ?19,    \
                                    ?20,    \
                                    ?21)"


//******************************************************************//
// select SQL queries
//******************************************************************//
#define SELECT_DBInfo                       @"SELECT * FROM DBInfo"
#define SELECT_CDNInfo_ByCdnPathNo          @"SELECT * FROM CDNInfo where cdnPathNo = %d"

#define SELECT_SaleCategoryList             @"SELECT * FROM SaleCategoryList order by saleCategoryOrder"
#define SELECT_UseCategoryList              @"SELECT * FROM UseCategoryList order by useCategoryOrder"

#define SELECT_FlowerInfoList_All           @"SELECT * FROM FlowerInfoList"
#define SELECT_FlowerInfoList_BySaleCate    @"SELECT * FROM FlowerInfoList WHERE saleCategoryNo = %d order by categoryOrder"
#define SELECT_FlowerInfoList_ByUseCate     @"SELECT * FROM FlowerInfoList WHERE useCategoryNo  = %d order by categoryOrder"

#define SELECT_FavoriteList                 @"SELECT * FROM FavoriteList order by favoritedDate desc"



//******************************************************************//
// delete SQL queries
//******************************************************************//
#define DELETE_CDNInfo_All                  "DELETE FROM CDNInfo"
#define DELETE_SaleCategoryList_All         "DELETE FROM SaleCategoryList"
#define DELETE_UseCategoryList_All          "DELETE FROM UseCategoryList"
#define DELETE_FlowerInfoList_All           "DELETE FROM FlowerInfoList"
#define DELETE_FavoriteList_ByFlowerNo      "DELETE FROM FavoriteList WHERE flowerNo = %d"

//******************************************************************//
// drop SQL queries
//******************************************************************//
#define DROP_CDNInfo                        @"DROP TABLE IF EXISTS CDNInfo"
#define DROP_SaleCategoryList               @"DROP TABLE IF EXISTS SaleCategoryList"
#define DROP_UseCategoryList                @"DROP TABLE IF EXISTS UseCategoryList"
#define DROP_FlowerInfoList                 @"DROP TABLE IF EXISTS FlowerInfoList"
//#define DROP_SubInfoList                    @"DROP TABLE IF EXISTS FlowerSubInfoList"
#define DROP_FavoriteList                   @"DROP TABLE IF EXISTS FavoriteList"

#endif
